Imports System.Data.SqlClient
Imports System.Text

Friend Class DBConnectLog_Factory
    Inherits DBBase

    Public Function AverageDayConnectionCounts( _
                    ByVal sDay As String, _
                    ByVal lDatabaseID As Long) As DataTable

        Return AverageDayConnectionCounts(sDay, lDatabaseID, 0)
    End Function

    Public Function AverageDayConnectionCounts( _
                        ByVal sDay As String, _
                        ByVal lDatabaseID As Long, _
                        ByVal iDaysToAverage As Integer) As DataTable

        Dim oData As New DataTable

        Dim sSQL As String = "p_AverageDayConnectionCount"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        Dim oParm As SqlParameter

        oParm = New SqlParameter("@Date", SqlDbType.DateTime)
        oParm.Value = sDay
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@DatabaseID", SqlDbType.BigInt)
        oParm.Value = lDatabaseID
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@DaysToInclude", SqlDbType.Int)
        oParm.Value = iDaysToAverage
        oCMD.Parameters.Add(oParm)

        Dim oDT As New SqlDataAdapter(oCMD)

        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        oDT.Dispose()

        Return oData
    End Function

    Public Function AverageDailyConnectionCounts( _
                            ByVal sWeek As String, _
                            ByVal lDatabaseID As Long) As DataTable

        Return AverageDailyConnectionCounts(sWeek, lDatabaseID, 0)
    End Function

    Public Function AverageDailyConnectionCounts( _
                            ByVal sWeek As String, _
                            ByVal lDatabaseID As Long, _
                            ByVal lWeeksToInclude As Integer) As DataTable

        Dim oData As New DataTable

        Dim sSQL As String = "p_AverageDailyConnectionCount"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        Dim oParm As SqlParameter

        oParm = New SqlParameter("@DateBeginning", SqlDbType.DateTime)
        oParm.Value = sWeek
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@DatabaseID", SqlDbType.BigInt)
        oParm.Value = lDatabaseID
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@WeeksToInclude", SqlDbType.Int)
        oParm.Value = lWeeksToInclude
        oCMD.Parameters.Add(oParm)

        Dim oDT As New SqlDataAdapter(oCMD)

        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        oDT.Dispose()

        Return oData
    End Function

    Public Function AverageWeeklyConnectionCounts( _
                    ByVal lCountOfWeeks As Long, _
                    ByVal lDatabaseID As Long) As DataTable

        ' FIX: 2007-10-29 JCL DB function dbo.WeekBeginning missing from standard build.

        Dim oData As New DataTable

        Dim sSQL As String = "p_AverageWeeklyConnectionCount"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        Dim oParm As SqlParameter

        oParm = New SqlParameter("@CountOfWeeks", SqlDbType.Int)
        oParm.Value = lCountOfWeeks
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@DatabaseID", SqlDbType.BigInt)
        oParm.Value = lDatabaseID
        oCMD.Parameters.Add(oParm)

        Dim oDT As New SqlDataAdapter(oCMD)

        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        oDT.Dispose()

        Return oData
    End Function

    Public Function ConnectionIsStillAlive( _
                        ByVal lDatabaseID As Long, _
                        ByVal lSPID As Long, _
                        ByVal sHostName As String, _
                        ByVal sLoginName As String, _
                        ByVal sStatus As String, _
                        ByVal lBlockID As Long, _
                        ByVal sCommand As String) As Long

        Dim oData As New DataTable

        Dim sSQL As String = "p_ConnectionStillAlive"

        Dim oCon As New SqlConnection(msConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        oCMD.CommandType = CommandType.StoredProcedure

        Dim oParm As SqlParameter

        oParm = New SqlParameter("@DatabaseID", SqlDbType.BigInt)
        oParm.Value = lDatabaseID
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@Connect_SPID", SqlDbType.BigInt)
        oParm.Value = lSPID
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@Connect_HostName", SqlDbType.VarChar, 255)
        oParm.Value = sHostName
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@Connect_LoginName", SqlDbType.VarChar, 255)
        oParm.Value = sLoginName
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@Connect_Status", SqlDbType.VarChar, 255)
        oParm.Value = sStatus
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@Connect_BlockID", SqlDbType.BigInt)
        oParm.Value = lBlockID
        oCMD.Parameters.Add(oParm)

        oParm = New SqlParameter("@Connect_Command", SqlDbType.VarChar, 255)
        oParm.Value = sCommand
        oCMD.Parameters.Add(oParm)

        Dim oValue As Object = oCMD.ExecuteScalar

        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        If Not oValue Is System.DBNull.Value Then
            Return Val(oValue)
        Else
            Return NODATA
        End If
    End Function

    Public Function ConnectionsInPeriod( _
                    ByVal lDatabaseID As Long, _
                    ByVal sStartDate As Date, _
                    ByVal sEndDate As Date) As DataTable

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append("   Connect_SPID,")
            .Append("   Connect_Status,")
            .Append("   Connect_HostName,")
            .Append("   Connect_BlockID,")
            .Append("   Connect_Command,")
            .Append("   Connect_LoginName,")
            .Append("   Connect_Current,")
            .Append("   Connect_IsHostProcess,")
            .Append("   min(CreateDate) 'CreateDate',")
            .Append("   max(LastDetectDate) 'LastDetectDate' ")
            .Append(" from")
            .Append("   ConnectHistory")
            .Append(" where")
            .Append("   databaseid = " & lDatabaseID)
            .Append("   and [CreateDate] < " & ValueToDBValue(sEndDate))
            .Append("   and [lastdetectdate] > " & ValueToDBValue(sStartDate))
            .Append("   and Connect_IsHostProcess=0")
            .Append(" group by")
            .Append("   DatabaseID,")
            .Append("   Connect_SPID,")
            .Append("   Connect_Status,")
            .Append("   Connect_HostName,")
            .Append("   Connect_BlockID,")
            .Append("   Connect_Command,")
            .Append("   Connect_LoginName,")
            .Append("   Connect_Current,")
            .Append("   Connect_IsHostProcess")
            .Append(" order by")
            .Append("   min(CreateDate),")
            .Append("   max(LastDetectDate)")
        End With

        Return MyBase.ExecuteReadToDataTable(sSQL.ToString)
    End Function

    Public Sub RemoveExpiredData(ByVal iExpirationPeriod As Integer)
        Dim sSQL As New StringBuilder("delete from dbo.ConnectHistory")
        sSQL.Append(" where")
        sSQL.Append("   datediff(day,[CreateDate],current_timestamp)>" & iExpirationPeriod)

        MyBase.ExecuteNonQuery(sSQL.ToString)
    End Sub
End Class
